package vn.vntracking.truck;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.Date;

import org.smslib.helper.Logger;

/**
 * Get information address of device from http://truck.vntracking.vn <br>
 * DB: vntracking2<br>
 * driver = "org.gjt.mm.mysql.Driver"<br>
 * url = "jdbc:mysql://123.26.255.11/vntracking2"<br>
 * username = "root"<br>
 * password = "admin-vntk@hptel.com!"<br>
 * @author Vara 
 * @category Hand made production
 * 
 *   
 */
public class DB{

	private static Connection conn = null;
	private static PreparedStatement pst = null;
	private static ResultSet rs = null;
	private static String query = null;
	public static DB dB = new DB();

	public static Connection getConnection() throws Exception{

		String driver = "org.gjt.mm.mysql.Driver";
		String url = "jdbc:mysql://123.26.255.11/vntracking2";
		String username = "root";
		String password = "admin-vntk@hptel.com!";

		Class.forName(driver);
		conn = DriverManager.getConnection(url, username, password);
		return conn;
	}

	/**
	 * Get DeviceGroup from userID parents
	 * @param accountID
	 * @param userID
	 * @return all device or group device or no device
	 */

	public static String getDeviceGroup(String accountID, String userID){

		try{
			conn = getConnection();
			conn.setAutoCommit(false);
			query = "SELECT userID, groupID FROM grouplist WHERE accountID = \'" + accountID + "\'";

			pst = conn.prepareStatement(query);

			rs = pst.executeQuery();

			while (rs.next()){
				String temp = rs.getString(1);
				if(temp.equals(userID)){
					return rs.getString(2);
				}
			}

			/**
			 * Get "SELECT" cause anonymous, this is "userID"
			 */
			query = "SELECT userID FROM user WHERE accountID = \'" + accountID + "\'";
			pst = conn.prepareStatement(query);
			rs = pst.executeQuery();
			while(rs.next()){
				String temp = rs.getString("userID");
				if(temp.equals(userID)){
					return "all";
				}
			}
			
			/**
			 * User Administrator, this's special user. 
			 * If it isn't create in "User manager" then it's in account table in DB vntracking
			 */
			if(userID.equals("admin"))
				return "admin";

			return "nouser";

		} catch (Exception e) {
			Logger.getInstance().logVara(e.toString(), null, null);
			e.printStackTrace();
			closeConnection();
			return "error";
		}
	}
	
	/**
	 * Check pass of userID
	 * @param accountID
	 * @param userID
	 * @param pass
	 * @return true or false
	 */
	public static boolean checkPass(String accountID, String userID, String pass){

		try {
			Logger.getInstance().logVara("Checking password...", null, null);
			conn = getConnection();
			conn.setAutoCommit(false);
			query = "SELECT password FROM user WHERE accountID=\'" + accountID + "\'" + "and userID=\'" + userID +"\'";
			pst = conn.prepareStatement(query);

			rs = pst.executeQuery();
			while(rs.next()){
				String temp = rs.getString(1);
				if(temp.equals(pass))
					return true;
				else {
					Logger.getInstance().logVara("Password false!", null, null);
					return false;
				}
			}
		} 
		catch (Exception e) {
			e.printStackTrace();
			closeConnection();
		}
		return false;
		
	}

	/**
	 * Convert number plate to deviceID 
	 * Example: tasa_hpg 16L-5186 to 5186
	 * @param accountID
	 * @param numPlate
	 * @return
	 */
	public String numPlateToDeviceID(String accountID, String numPlate){

		try {
			conn = getConnection();
			conn.setAutoCommit(false);
			query = "SELECT deviceID FROM device WHERE accountID=\'" + accountID + "\'" + " AND description=\'" + numPlate + "\'";
			pst = conn.prepareStatement(query);

			rs = pst.executeQuery();
			while(rs.next()){
				return rs.getString(1);
			}
		} 
		catch (Exception e) {
			e.printStackTrace();
			closeConnection();
		}
		// TODO: System.out.println("No this number plate!");
		return null;
	}

	/**
	 * Get address
	 * @param accountID
	 * @param deviceID
	 * @return
	 */
	public static String getAddress (String accountID, String deviceID){

		try {
			Logger.getInstance().logVara("Getting address..", null, null);
			conn = getConnection();
			conn.setAutoCommit(false);
			query = "SELECT timestamp FROM eventdata WHERE accountID=\'" + accountID + "\'" + "AND deviceID=\'" + deviceID +"\'";
			pst = conn.prepareStatement(query);

			rs = pst.executeQuery();
			String timestamp = null;
			rs.last();
			timestamp = rs.getString(1);
			query = "SELECT speedKPH, address FROM eventdata WHERE accountID=\'" + accountID + "\'" + "AND deviceID=\'" + deviceID +"\'" + "AND timestamp=\'" + timestamp + "\'";
			pst = conn.prepareStatement(query);

			rs = pst.executeQuery();
			while(rs.next()) {
				new Unicode2Nosign();
				return timestampToDateTime(timestamp) + " "  + rs.getString(1) + " km/h " +"\n"+ Unicode2Nosign.convert(rs.getString(2));
			}

		} catch (Exception e) {
			e.printStackTrace();
			closeConnection();
		}
		Logger.getInstance().logVara("Can't get address!", null, null);
		return null;
	}

	public static String timestampToDateTime(String timestamp){

		long timestampLong = new Long(timestamp);
		return new java.text.SimpleDateFormat("dd/MM/yyyy HH:mm:ss").format(new Date (timestampLong*1000));

	}

	/**
	 * Main function
	 * @param accountID
	 * @param userID
	 * @param pass
	 * @param deviceID
	 * @return address device
	 */
	public String checkDevice (String accountID, String userID, String pass, String deviceID){

		accountID = accountID.toLowerCase();
		userID = userID.toLowerCase();
		
		String result = getDeviceGroup(accountID, userID);
		
		if(result.equals("error"))
			return null;
		else if(result.equals("nouser"))
			return null;
		else if(result.equals("admin")){
			try {
				conn = getConnection();
				conn.setAutoCommit(false);
				query = "SELECT password FROM account WHERE accountID=\'" + accountID + "\'";
				pst = conn.prepareStatement(query);
				rs = pst.executeQuery();
				while (rs.next()){
					String temp = rs.getString(1);
					if(temp.equals(pass))
						return getAddress(accountID, deviceID);
				}
					
			} catch (Exception e) {
				closeConnection();
				e.printStackTrace();
			}
		}
		else if(checkPass(accountID, userID, pass)){
			if(result.equals("all")){
				// User manager all device
				try {
					conn = getConnection();
					conn.setAutoCommit(false);
					query = "SELECT groupID FROM devicelist WHERE accountID=\'" + accountID + "\'" + "and deviceID=\'" + deviceID +"\'";
					pst = conn.prepareStatement(query);

					rs = pst.executeQuery();
					while(rs.next()){
						return getAddress(accountID, deviceID);
					}
				} catch (Exception e) {
					e.printStackTrace();
					closeConnection();
				}
			}
			else {
				// User manager one device group
				try{
					conn = getConnection();
					conn.setAutoCommit(false);
					query = "SELECT groupID FROM devicelist WHERE accountID=\'" + accountID + "\'" + "and deviceID=\'" + deviceID +"\'";
					pst = conn.prepareStatement(query);

					rs = pst.executeQuery();
					while(rs.next()){
						String temp = rs.getString(1);
						if(temp.equals(result))
							return getAddress(accountID, deviceID);
					}

				}catch(Exception e){
					e.printStackTrace();
					closeConnection();
					return "error";
				}
			} 

		}
		Logger.getInstance().logVara("Check Devices false!", null, null);
		return null;
	}
	private static void closeConnection(){
		try	{
			if (conn != null) conn.close();
		}
		catch (Exception e)	{
		}
		finally	{
			conn = null;
		}
	}
}
